package com.teamwings.modules.buffet.controller;


import cn.hutool.core.bean.BeanUtil;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.teamwings.common.exception.ServiceException;
import com.teamwings.common.vo.Result;
import com.teamwings.modules.buffet.entity.FormTableCtrlSql;
import com.teamwings.modules.buffet.service.IFormTableCtrlSqlService;
import com.teamwings.modules.head.service.IFormTableHeadService;
import com.teamwings.modules.page.service.IPageService;
import io.swagger.annotations.Api;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.ArrayUtils;
import org.apache.commons.lang3.ObjectUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.shiro.SecurityUtils;
import org.hibernate.procedure.NoSuchParameterException;
import org.jeecg.common.system.api.ISysBaseAPI;
import org.jeecg.common.system.vo.LoginUser;
import org.jeecg.common.workflow.api.IWorkFlowBaseExtAPI;
import org.jeecg.common.workflow.api.vo.WorkFlowFormInfoVO;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.bind.annotation.*;
import java.io.UnsupportedEncodingException;
import java.net.URLDecoder;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * <p>
 *  表单自助接口
 * </p>
 *
 * 说明文档：
 * https://www.yuque.com/docs/share/1c5e49cb-db12-4a4d-9ca0-3edcef61d981
 *
 * @author wuyc
 * @since 2021-08-26
 */
@Slf4j
@RestController
@Api(tags = "表单自助接口")
@RequestMapping("/form/buffet")
public class FormTableCtrlBuffetController {

    public static final String VAR_OR = "#or";
    public static final String OP_LIKE = "like";
    public static final String[] OP_RANGE = new String[]{">", "<", ">=", "<=", "<>",
            "!=", "in", "like", "not in"};
    public static final String REG_COMMENT = "/\\*\\*/";
    public static final String REG_NOTNAME = "[\\s ;']";
    public static final String REG_VARIABLE = "[#$&]([\\w#$&\\.]+)";
    public static final String PREFIX_CHILDSQL = "##";
    public static final String PREFIX_FIELD = "#";
    public static final String PREFIX_PLAIN = "$";
    public static final String PREFIX_DYNAMIC = "$$";
    public static final String PREFIX_BUILTIN = "&";
    public static final String DY_COND_PARAMS = "#params";
    public static final String PREFIX_OP = "#op";
    public static final String OP_AND = " AND ";
    public static final String OP_OR = " OR ";
    public static final String OP_IN = "in";
    public static final String OP_NOT_IN = "not in";

    @Autowired
    private IFormTableCtrlSqlService formTableCtrlSqlService;

    @Autowired
    private IPageService pageService;
    @Autowired
    private ISysBaseAPI sysBaseAPI;
    @Autowired
    private IWorkFlowBaseExtAPI workFlowBaseExtAPI;

    /**
     * 保存查询语句
     * @param args  {id, sql, name, ctrlName}
     * @return
     */
    @PostMapping(value = "/save")
    public Result<?> save(@RequestBody FormTableCtrlSql ctrlSql) {
        boolean notEnoughArgument = false;
        if (ctrlSql.getSql() == null || ctrlSql.getName() == null) {
            notEnoughArgument = true;
        }
        // if (ctrlSql.getCtrlName() == null && ctrlSql.getTableHeadId() == null) {
        // if (this.totalNullOrEmpty(ctrlSql.getCtrlName(), ctrlSql.getTableHeadId())) {
        //     notEnoughArgument = true;
        // }

        if (notEnoughArgument) {
            return Result.error("参数不足");
        }

        boolean saveResult = false;
        String id = ctrlSql.getId();
        if (StringUtils.isNotBlank(id) && !id.equals("-1") && !id.equals("0")) {
            ctrlSql.setUpdateTime(new Date());
            saveResult = formTableCtrlSqlService.updateById(ctrlSql);
        } else {
            ctrlSql.setId(null);
            saveResult = formTableCtrlSqlService.save(ctrlSql);
        }

        if (saveResult) {
            return Result.OK("保存成功", ctrlSql.getId());
        } else {
            return Result.error("保存失败");
        }
    }

    /**
     * 删除查询语句
     * @param id            编号
     * @param ctrlName      控件类型名
     * @return
     */
    @PostMapping(value = "/delete")
    public Result<?> delete(@RequestBody String json) {
        // boolean notEnoughArgument = false;
        JSONObject jo = (JSONObject) JSONObject.parse(json);
        String id = jo.getString("id");
        // String ctrlName = jo.getString("ctrlName");
        // String tableHeadId = jo.getString("tableHeadId");
        // if (id == null || this.totalNullOrEmpty(ctrlName, tableHeadId)) {
        if (id == null) {
            return Result.error("参数不足");
        }

        FormTableCtrlSql byId = formTableCtrlSqlService.getById(id);
        if (byId == null) {
            return Result.error("找不到给出编号的控件");
        }
        // if (byId.getCtrlName().equals(ctrlName) == false) {
        //     return Result.error("找不到给出类型的控件");
        // }

        boolean b = formTableCtrlSqlService.removeById(id);

        if (b) {
            return Result.ok("删除成功！");
        } else {
            return Result.error("删除失败");
        }
    }

    /**
     * 获取SQL分页列表
     * @param params {page, pageSize, ctrlName}
     * @return
     */
    @PostMapping(value = "/list")
    public Result<?> list(@RequestBody JSONObject params) {
        Integer page = params.getInteger("page");
        Integer pageSize = params.getInteger("pageSize");
        String ctrlName = params.getString("ctrlName");
        String tableHeadId = params.getString("tableHeadId");

        // if (page == null || pageSize == null
        //         || (this.totalNullOrEmpty(ctrlName, tableHeadId))) {
        if (page == null || pageSize == null) {
            return Result.error("参数不足。");
        }

        Page<FormTableCtrlSql> ctrlPage = new Page<>(page, pageSize);
        // LambdaQueryWrapper<FormTableCtrlSql> queryWrapper = new LambdaQueryWrapper<>();
        // queryWrapper
        //         .eq(FormTableCtrlSql::getCtrlName, ctrlName)
        //         .orderByDesc(FormTableCtrlSql::getCreateTime);
        // IPage<FormTableCtrlSql> pageData = formTableCtrlSqlService.page(ctrlPage, queryWrapper);
        IPage<FormTableCtrlSql> list = null;
        // 获取控件级别的语句
        if (StringUtils.isNotBlank(ctrlName)) {
            list = formTableCtrlSqlService.getListByCtrl(ctrlPage, ctrlName);
        } else if (StringUtils.isNotBlank(tableHeadId)){
            // 获取表单级别的语句
            list = formTableCtrlSqlService.getListByFormTable(ctrlPage, tableHeadId);
        } else {
            // 获取全局级别的语句
            list = formTableCtrlSqlService.getCommonList(ctrlPage);
        }

        if (list != null) {
            return Result.OK("查询成功", list);
        } else {
            return Result.error("查询失败，参数不足");
        }
    }

    /**
     * 获取某个SQL详细
     * @param id
     * @return
     */
    @PostMapping(value = "/detail")
    public Result<?> detail(@RequestBody String id) {
        // 查找sql
        FormTableCtrlSql ctrlSql = formTableCtrlSqlService.getById(id);
        if (ctrlSql == null) {
            return Result.error("找不到给出编号的控件");
        }
        return Result.OK(ctrlSql);
    }

    /**
     * =============================== namedParameterJdbcTemplate 预编译SQL 防注入 ========================================
     */
    @PostMapping("/exec")
    public Result<?> exec(@RequestBody String args) {
        JSONObject retJson = new JSONObject();
        JSONObject arg = (JSONObject)JSON.parse(args);
        String id = arg.getString("id");
        JSONObject params = arg.getJSONObject("params");
        if (params == null) {
            params = new JSONObject();
        }
        // 表单实例id
        String formId = arg.getString("form");
        // 表单id
        String headId = arg.getString("headId");

        // 查询语句判断
        Map<String, Object> rg = new HashMap<>();
        String sql = fromSql(id);
        if (StringUtils.isBlank(sql)) {
            return Result.error("执行语句为空");
        }

        sql = sql.replaceAll("^[\n ]+", "").trim();

        sql = formatSql(formId, headId, sql, params, rg);
        if (sql.toLowerCase().startsWith("select")) {
            if (arg.containsKey("page")) {
                Integer page = arg.getInteger("page");
                Integer pagesize = 15;
                if (arg.containsKey("pagesize")) {
                    pagesize = arg.getInteger("pagesize");
                }
                String countSql = "SELECT COUNT(1) FROM (" + sql + ")";
                System.out.println("countSql: " + countSql);
                List<Map<String, Object>> countResult = pageService.querySql(countSql, rg);
                Integer total = Integer.parseInt(String.valueOf(countResult.get(0).get("count")));
                // 分页修正
                if (page < 1) {
                    page = 1;
                }
                if (total == 0) {
                    page = 1;
                } else if (page * pagesize > total) {
                    page = total / pagesize;
                    if (total % pagesize > 0) {
                        page += 1;
                    }
                }

                // 实际分页
                String pageSql = sql + " LIMIT " + ((page - 1) * pagesize) + ", " + pagesize;
                System.out.println("pageSql: " + pageSql);
                List<Map<String, Object>> pageResult = pageService.querySql(pageSql, rg);
                retJson.put("page", page);
                retJson.put("pagesize", pagesize);
                retJson.put("pagecount", Math.round(Math.ceil((float)total / pagesize)));
                retJson.put("total", total);
                retJson.put("list", pageResult);
            } else {
                System.out.println("normalSql: " + sql);
                List<Map<String, Object>> list = pageService.querySql(sql, rg);
                retJson.put("list", list);
            }
            return Result.OK("执行完成", retJson);
        } else {
            // 非select
            System.out.println("normalSql: " + sql);
            return Result.OK("执行完成", pageService.querySql(sql, rg));
        }
    }

    // 根据ID获取SQL
    private String fromSql(String id) {
        FormTableCtrlSql ctrlSql = formTableCtrlSqlService.getById(id);
        String sql = new String(Base64.getDecoder().decode(ctrlSql.getSql()));
        try {
            sql = URLDecoder.decode(sql, "UTF-8");
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }
        return sql;
    }

    // 格式化SQL
    private String formatSql(String formId, String headId, String sql, JSONObject params, Map<String, Object> rg) {
        // [#$&]param 匹配
        Pattern pattern = Pattern.compile(REG_VARIABLE);
        Matcher matcher = pattern.matcher(sql);
        while (matcher.find()) {
            String content = matcher.group(0);
            System.out.println("匹配参数样式：" + content);
            String key;
            if (content.startsWith(PREFIX_CHILDSQL)) {
                // ## 子SQL
                key = content.substring(2);
                if (!params.containsKey("#" + key)) {
                    throw new NoSuchParameterException("不存在参数 '#" + key + "'");
                }
                String id = params.getString(key);
                JSONObject tempParams = params.getJSONObject("#" + key);
                String childSql = fromSql(id);
                if (childSql != null) {
                    // ## 替换子sql
                    String chs = formatSql(formId, headId, childSql, tempParams, rg);
                    sql = sql.replace(content, chs);
                }
            } else if (content.startsWith(PREFIX_FIELD)) {
                // # 名称或值内容
                key = content.substring(1);
                if (!params.containsKey(key)) {
                    throw new NoSuchParameterException("不存在参数 '" + key + "'");
                }
                // 如果是数组 则进行参数拼接
                if (params.get(key) instanceof JSONArray) {
                    //拼接 in 为查询条件的参数
                    String inParams = getInParams((JSONArray) params.get(key));
                    sql = sql.replace(content, ":" + key);
                    // 存入参数
                    rg.put(key, inParams);
                    // 跳过本轮
                    continue;
                }
                // 过滤参数内容
                String paramContent = params.get(key) + "";
                paramContent = this.injectName(paramContent);
                // 替换生成sql exp: select * from xxx where id = #id => select * from xxx where id = :id
                sql = sql.replace(content, ":" + key);
                // 存入参数
                rg.put(key, paramContent);
            } else if (content.startsWith(PREFIX_DYNAMIC)) {
                // $$ 动态条件
                key = content.substring(2);
                // key => where
                JSONArray arr = params.getJSONArray(key);
                String tpSql;
                if (arr == null) {
                    tpSql = " 1 = 1 ";
                } else {
                    tpSql = this.dynamicSql(formId, headId, arr, OP_AND, rg);
                }
                sql = sql.replaceAll("\\$\\$" + key, tpSql);
            } else if (content.startsWith(PREFIX_PLAIN)) {
                // $ 宽松内容 ====== 未作修改
                key = content.substring(1);
                if (!params.containsKey(key)) {
                    throw new NoSuchParameterException("不存在参数 '"+key+"'");
                }
                // 过滤
                String paramContent = params.get(key).toString();
                paramContent = this.injectValue(paramContent);
                sql = sql.replace(content, paramContent);
            } else if (content.startsWith(PREFIX_BUILTIN)) {
                // TODO 内置变量 &user, &dept, &flow, &sys
                // & 内置内容
                key = content.substring(1);
                //内置变量实现
                sql = getBuiltInCondition(formId, headId, sql, content, key);
            }
        }
        return sql;
    }

    // 动态where SQL
    private String dynamicSql(String formId, String headId, JSONArray jsonArray, String conditionOperator, Map<String, Object> rg) {
        if (conditionOperator == null) {
            conditionOperator = OP_AND;
        }
        StringBuilder sb = new StringBuilder();
        // 用于单个条件同名情况下 => (a > :k1 or a < k2)
        for (Object item : jsonArray) {
            if (sb.length() > 1) {
                sb.append(conditionOperator);
            }
            // 得到条件对象
            JSONObject joItem = (JSONObject) item;
            // 判断是否存在 '#or'
            if (joItem.containsKey(VAR_OR)) {
                JSONArray jaOr = joItem.getJSONArray(VAR_OR);
                sb.append("(");
                sb.append(this.dynamicSql(formId, headId, jaOr, OP_OR, rg));
                sb.append(") ");
            } else if (joItem.containsKey(DY_COND_PARAMS)) {
                // 判断是否子查询
                // 查找字段键名
                Optional<String> key = joItem.keySet().stream()
                        .filter((keyItem) -> keyItem.charAt(0) != '#')
                        .findFirst();
                if (!key.isPresent()) {
                    continue;
                }
                String sqlIdKey = key.get();
                String sqlId = joItem.getString(sqlIdKey);
                JSONObject params = joItem.getJSONObject(DY_COND_PARAMS);
                String childSql = this.fromSql(sqlId);
                // 格式化sql => :param ~
                childSql = formatSql(formId, headId, childSql, params, rg);

                // 拼接
                String op = joItem.getString(PREFIX_OP);
                if (!ArrayUtils.contains(OP_RANGE, op)) {
                    log.error("动态查询条件运算符有误，传入：" + op);
                    op = "=";
                }
                sb.append(sqlIdKey);
                sb.append(" ");
                sb.append(op);
                sb.append(" (");
                sb.append(childSql);
                sb.append(") ");
            } else {
                // 单个条件, 查找条件运算符
                String op;
                String key;
                String val;

                // 查找字段键名
                Optional<String> first = joItem.keySet().stream()
                        .filter((keyItem) -> keyItem.charAt(0) != '#')
                        .findFirst();
                if (!first.isPresent()) {
                    continue;
                }
                key = first.get();
                Object objVal = joItem.get(key);
                op = joItem.getString(PREFIX_OP);
                if (!ArrayUtils.contains(OP_RANGE, op)) {
                    log.error("动态查询条件运算符有误，传入：" + op);
                    op = "=";
                }

                // in 为查询条件
                if (op.equals(OP_IN) || op.equals(OP_NOT_IN)){
                    if (!(objVal instanceof JSONArray)) {
                        throw new ServiceException("动态查询使用in或者not in，参数需要为数组");
                    }
                    //拼接 in 为查询条件的参数
                    String inParams = getInParams((JSONArray) objVal);
                    rg.put(key, inParams);
                    sb.append(key);
                    sb.append(" ");
                    sb.append(op);
                    sb.append(" ('");
                    sb.append(":").append(key);
                    sb.append("')");
                    // 跳过本轮
                    continue;
                }

                // 区分字符串或是数值的处理
                if (objVal instanceof String) {
                    String st = this.injectValue(objVal + "");
                    String sk = key;
                    if (rg.containsKey(sk)) {
                        // 同名不同参数情况下区分编号
                        sk += System.currentTimeMillis();
                    }
                    rg.put(sk, st);
                    if (op.equals(OP_LIKE)) {
                        val = "concat('%', :" + sk + ", '%')";
                    } else {
                        val = ":" + sk;
                    }
                } else {
                    String sk = key;
                    if (rg.containsKey(sk)) {
                        // 同名不同参数情况下区分编号
                        sk += System.currentTimeMillis();
                    }
                    rg.put(sk, Float.valueOf(objVal + ""));
                    val = ":" + sk;
                }

                sb.append(key);
                sb.append(" ");
                sb.append(op);
                sb.append(" ");
                sb.append(val);
                sb.append(" ");
            }
        }
        return sb.toString();
    }

// =================================================================================================

    /**
     * 执行语句
     * @param args 参数，有 ctrlName和 id
     * @return
     */
    @PostMapping(value = "/execute")
    public Result<?> execute(@RequestBody String args) {
        JSONObject jo = (JSONObject)JSON.parse(args);
        // String ctrlName = jo.getString("ctrlName");
        String id = jo.getString("id");
        JSONObject params = jo.getJSONObject("params");
        if (params == null) {
            params = new JSONObject();
        }
        // 表单实例id
        String formId = jo.getString("form");
        // 表单id
        String headId = jo.getString("headId");
        String sql = this.getSql(id,formId,headId, params);

        if (sql == null) {
            return Result.error("找不到给出编号的控件");
        }

        if (StringUtils.isBlank(sql)) {
            return Result.error("执行的语句为空。");
        }

        // 是查询语句
        sql = sql.replaceAll("^[\n ]+", "").trim();

        if (sql.toLowerCase().startsWith("select")) {
            JSONObject retJson = new JSONObject();
            // 检查是否有分页
            if (jo.containsKey("page")) {
                Integer page = jo.getInteger("page");
                Integer pagesize = 15;
                if (jo.containsKey("pagesize")) {
                    pagesize = jo.getInteger("pagesize");
                }
                String countSql = "SELECT COUNT(*) FROM (" + sql + ")";
                // 总数据量
                List<Map> countResult = pageService.executeSql(countSql);
                Integer total = Integer.parseInt(countResult.get(0).get("COUNT") + "");
                // 分页修正
                if (page < 1) page = 1;
                if (total == 0) {
                    page = 1;
                }else if (page * pagesize > total) {
                    page = total / pagesize;
                    if (total % pagesize > 0) {
                        page += 1;
                    }
                }

                // 实际分页
                String pageSql = sql + " LIMIT " + ((page - 1) * pagesize) + ", " + pagesize;
                List<Map> pageResult = pageService.executeSql(pageSql);
                // retJson.put("page", )
                retJson.put("page", page);
                retJson.put("pagesize", pagesize);
                retJson.put("pagecount", Math.round(Math.ceil((float)total / pagesize)));
                retJson.put("total", total);
                retJson.put("list", pageResult);
            } else {
                List<Map> result = pageService.executeSql(sql);
                retJson.put("list", result);
            }
            // return Result.OK("执行完成", result);
            return Result.OK("执行完成", retJson);
        } else {
            Object result = pageService.executeSql(sql);
            return Result.OK("执行完成", result);
        }
    }

    /**
     * 合并SQL参数
     * @param sql
     * @return
     */
    private String parseSql(String formId,String headId, String sql, JSONObject params) {
        // 查找参数
        String retSql = this.getSqlArgs(formId, headId, sql, params);
        log.info(String.format("解析SQL\nSQL模板: %s\n参数：%s\n结果：%s",
                sql, params.toJSONString(), retSql));
        return retSql;
    }

    /**
     * 解析SQL语句中的参数
     * @param sql
     * @return
     */
    private String getSqlArgs(String formId, String headId, String sql, JSONObject params) {
        Pattern pattern = Pattern.compile(REG_VARIABLE);
        Matcher matcher = pattern.matcher(sql);

        while(matcher.find()) {
            String s = matcher.group(0);
            System.out.println(s);
            String key = null;

            if (s.startsWith(PREFIX_CHILDSQL)) {
                // ## 子SQL
                key = s.substring(2);
                if (!params.containsKey("#" + key)) {
                    throw new NoSuchParameterException("不存在参数 '#"+key+"'");
                }
                String sqlId = params.getString(key);
                String childSql = this.getSql(sqlId,null,null, params.getJSONObject("#" + key));
                if (childSql != null){
                    sql = sql.replace(s, childSql);
                }
            } else if (s.startsWith(PREFIX_FIELD)) {
                // # 名称或值内容
                key = s.substring(1);
                if (!params.containsKey(key)) {
                    throw new NoSuchParameterException("不存在参数 '"+key+"'");
                }
                // 如果是数组 则进行参数拼接
                if (params.get(key) instanceof JSONArray) {
                    //拼接 in 为查询条件的参数
                    String inParams = getInParams((JSONArray) params.get(key));
                    sql = sql.replace(s, inParams);
                    // 跳过本轮
                    continue;
                }
                // 过滤
                String paramContent = params.get(key) + "";
                paramContent = this.injectName(paramContent);
                sql = sql.replace(s, paramContent);
            } else if (s.startsWith(PREFIX_DYNAMIC)) {
                // $$ 动态条件
                sql = this.getFormDynamicCondition(sql, s, params);
            } else if (s.startsWith(PREFIX_PLAIN)) {
                // $ 宽松内容
                key = s.substring(1);
                if (!params.containsKey(key)) {
                    throw new NoSuchParameterException("不存在参数 '"+key+"'");
                }
                // 过滤
                String paramContent = params.get(key) + "";
                paramContent = this.injectValue(paramContent);
                sql = sql.replace(s, paramContent);
            } else if (s.startsWith(PREFIX_BUILTIN)) {
                // & 内置内容
                key = s.substring(1);
                //内置变量实现
                sql = getBuiltInCondition(formId,headId,sql,s,key);
            }
        }
        return sql;
    }



    /**
     * 过滤内容类型的字符串
     * @param paramContent
     * @return
     */
    private String injectValue(String paramContent) {
        paramContent = paramContent.replaceAll("'", "''");
        paramContent = paramContent.replaceAll(REG_COMMENT, "");
        return paramContent;
    }

    /**
     * 过滤名称类型的字符
     * @param paramContent
     * @return
     */
    private String injectName(String paramContent) {
        paramContent = paramContent.replaceAll(REG_NOTNAME, "");
        paramContent = paramContent.replaceAll(REG_COMMENT, "");
        return paramContent;
    }

    /**
     * 创建动态条件
     * @param sql 带占位符的查询语句
     * @param holder 当前动态占位符
     * @param params
     * @return
     */
    private String getFormDynamicCondition(String sql, String holder, JSONObject params) {
        String key = holder.substring(2);
        JSONArray jsonArray = params.getJSONArray(key);
        String partSql = null;
        if (jsonArray.size() == 0) {
            partSql = " 1=1 ";
        } else {
            partSql = this.getMultipleDynamicCondition(jsonArray, OP_AND);
        }
        sql = sql.replaceAll("\\$\\$" + key, partSql);
        return sql;
    }

    /**
     * 从数组参数中创建动态条件
     * @param jsonArray
     * @return
     */
    private String getMultipleDynamicCondition(JSONArray jsonArray, String conditionOperator) {
        if (conditionOperator == null) conditionOperator = OP_AND;
        StringBuilder sb = new StringBuilder();
        for (Object item :
                jsonArray) {

            if (sb.length() > 1) {
                sb.append(conditionOperator);
            }

            // 得到条件对象
            JSONObject joItem = (JSONObject) item;
            // 判断是否存在 '#or'
            if (joItem.containsKey(VAR_OR)) {
                JSONArray jaOr = joItem.getJSONArray(VAR_OR);
                sb.append("(");
                sb.append(this.getMultipleDynamicCondition(jaOr, OP_OR));
                sb.append(") ");
            } else if(joItem.containsKey(DY_COND_PARAMS)) {
                // 判断是否子查询
                // 查找字段键名
                Optional<String> key = joItem.keySet().stream()
                        .filter((keyItem) -> keyItem.charAt(0) != '#')
                        .findFirst();
                if (!key.isPresent()) {
                    continue;
                }
                String sqlIdKey = key.get();
                String sqlId = joItem.getString(sqlIdKey);
                JSONObject params = joItem.getJSONObject(DY_COND_PARAMS);
                String childSql = this.getSql(sqlId,null,null, params);

                // 拼接
                String op = joItem.getString(PREFIX_OP);
                if (!ArrayUtils.contains(OP_RANGE, op)) {
                    log.error("动态查询条件运算符有误，传入：" + op);
                    op = "=";
                }

                sb.append(sqlIdKey);
                sb.append(" ");
                sb.append(op);
                sb.append(" (");
                sb.append(childSql);
                sb.append(") ");
            } else {
                // 单个条件, 查找条件运算符
                Set<String> joItemKeys = joItem.keySet();
                String op = "=";
                String key = null;
                String val = null;

                // 查找字段键名
                Optional<String> first = joItem.keySet().stream()
                        .filter((keyItem) -> keyItem.charAt(0) != '#')
                        .findFirst();
                if (!first.isPresent()) {
                    continue;
                }
                key = first.get();
                Object objVal = joItem.get(key);
                op = joItem.getString(PREFIX_OP);
                if (!ArrayUtils.contains(OP_RANGE, op)) {
                    log.error("动态查询条件运算符有误，传入：" + op);
                    op = "=";
                }
                // in 为查询条件
                if (op.equals(OP_IN) || op.equals(OP_NOT_IN)){
                    if (!(objVal instanceof JSONArray)) {
                        throw new ServiceException("动态查询使用in或者not in，参数需要为数组");
                    }
                    //拼接 in 为查询条件的参数
                    String inParams = getInParams((JSONArray) objVal);
                    sb.append(key);
                    sb.append(" ");
                    sb.append(op);
                    sb.append(" ('");
                    sb.append(inParams);
                    sb.append("')");
                    // 跳过本轮
                    continue;
                }

                // 区分字符串或是数值的处理
                if (objVal instanceof String) {
                    val = this.injectValue(objVal + "");
                    if (op.equals(OP_LIKE)) {
                        val = "'%" + val + "%'";
                    } else {
                        val = "'" + val + "'";
                    }
                } else {
                    val = Float.valueOf(objVal + "") + "";
                }



                sb.append(key);
                sb.append(" ");
                sb.append(op);
                sb.append(" ");
                sb.append(val);
                sb.append(" ");
            }
        }
        return sb.toString();
    }

    // 合并需要执行的语句
    private String getSql(String sqlId, String formId, String headId, JSONObject params) {
        // 查找sql
        FormTableCtrlSql ctrlSql = formTableCtrlSqlService.getById(sqlId);
        if (ctrlSql == null) {
            return null;
        }
        // if (ctrlSql.getCtrlName().equals(ctrlName) == false) {
        //     return Result.error("找不到给出类型的控件");
        // }

        // 合并SQL
        String sql = new String(Base64.getDecoder().decode(ctrlSql.getSql()));
        try {
            sql = URLDecoder.decode(sql, "UTF-8");
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }
        if (StringUtils.isBlank(sql)) {
            return sql;
        }

        // TODO 检查权限

        // 查找参数值
        String combineSql = this.parseSql(formId,headId,sql, params);

        return combineSql;

        // // 根据传进来的参数“params”进行 “$” 号替换。
        // if (params != null) {
        //     Set<Map.Entry<String, Object>> entries = params.entrySet();
        //     for (Map.Entry<String, Object> entry : entries) {
        //         String key = entry.getKey();
        //         String value = (String) entry.getValue();
        //         value = value.replace("'", "''");
        //         String pattern  = "'$" + key;
        //         // 采用 单引号判断，如果 key关键字之前存在单引号，则允许带有注入的内容，并且会过滤掉单引号
        //         // 如果没有单引号，则代表是数值类型，会去掉数字与小数点以外的值
        //         if (sql.indexOf(pattern) == -1) {
        //             // 去掉数字以外的东西，如果出现两次.号，则会有问题
        //             value = value.replaceAll("[^\\d.]", "");
        //         }
        //         sql = sql.replace(pattern, value);
        //     }
        // }

        // 查找SQL语句中的“#”号进行数据替换
        /*
            #user, #flow, #sys
         */
        // return sql;
    }

    // 机体是否全部
    private boolean totalNullOrEmpty(String ...params) {
        for(String param : params) {
            if (StringUtils.isNotBlank(param)) {
                return false;
            }
        }
        return true;
    }

    @Autowired
    private IFormTableHeadService formTableHeadService;

    /**
     *  内置变量实现
     * @param formId 表单实例id
     * @param headId 表单headid
     * @param sql sql语句
     * @param s 含& 关键词
     * @param key 替换关键词
     * @return
     */
    private String getBuiltInCondition(String formId,String headId, String sql,String s, String key) {
        LoginUser sysUser = (LoginUser) SecurityUtils.getSubject().getPrincipal();
        // 用户
        if (key.startsWith("user")) {
            if ("user.id".equals(key)){
                sql = sql.replace(s,sysUser.getId());
            }else if ("user.name".equals(key)){
                sql = sql.replace(s,sysUser.getUsername());
            }
        // 部门
        }else if (key.startsWith("dept")){
            if ("dept.id".equals(key)){
                String depaId = sysBaseAPI.getDepartIdsByOrgCode(sysUser.getOrgCode());
                sql = sql.replace(s,depaId);
            }else if ("dept.name".equals(key)){
                List<String> name = sysBaseAPI.getDepartNamesByUsername(sysUser.getUsername());
                sql = sql.replace(s,name.get(0));
            }
        // 流程
        }else if (key.startsWith("flow")){
            if (StringUtils.isEmpty(formId)) {
                throw new ServiceException("form参数不能为空");
            }
            if (StringUtils.isEmpty(headId)) {
                throw new ServiceException("headId参数不能为空");
            }
            // 查询表单有没有对应的流程
            JdbcTemplate jdbcTemplate = pageService.getJdbcTemplate();
            String flowSql = "SELECT flow_id FROM sys_permission WHERE form_table_head_id = ?";
            String flow_id = jdbcTemplate.queryForObject(flowSql, new Object[]{headId}, String.class);

            if (StringUtils.isEmpty(flow_id)) {
                throw new ServiceException("找不到对应的流程");
            }
            // 获取对应当前流程信息
            org.jeecg.common.api.vo.Result<?> workFlowFormInfos = workFlowBaseExtAPI.getWorkFlowFormInfos(flow_id, headId, formId);
            if (ObjectUtils.isNotEmpty(workFlowFormInfos.getResult())) {

                WorkFlowFormInfoVO workFlowFormInfoVO = new WorkFlowFormInfoVO();
                BeanUtil.copyProperties(workFlowFormInfos.getResult(),workFlowFormInfoVO);

                if ("flow.id".equals(key)){
                    sql = sql.replace(s,workFlowFormInfoVO.getProgressId());
                }else if ("flow.itemId".equals(key)){
                    sql = sql.replace(s,workFlowFormInfoVO.getWorkItemId());
                }
            }
        // 系统
        }else if (key.startsWith("sys")){
            if ("sys.time".equals(key)){
                sql = sql.replace(s,System.currentTimeMillis()+"");
            }else if ("sys.formatTime".equals(key)){
                sql = sql.replace(s,DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss").format(LocalDateTime.now()));
            }
        }
        return sql;
    }

    /**
     * 拼接 in 为查询条件的参数
     * @param o
     * @return
     */
    private String getInParams(JSONArray o) {
        String param = "";
        //  使用迭代器遍历参数
        Iterator<Object> iterator = o.iterator();
        while (iterator.hasNext()) {
            param += iterator.next().toString()+"','";
        }
        return param.substring(0,param.length()-3);
    }

    public static void main(String[] args) {
        // String value = "sdf@412323###14.1as  \nds23";
        // value = value.replaceAll("[^\\d.]", "");
        // System.out.println(value);
        // value = "\n \n as dfasd fas";
        // System.out.println(value);
        // value = value.replaceAll("^[\n ]+", "");
        // System.out.println(value.trim());

        // String value = "SELECT a,b,c FROM abc WHERE d = '#flow.id' AND e = '#'";

        // SQL正则
        // String sql = "select * sys_log where log_type = #type and method" +
        //         " like '%$method%' or create_by = '#uid' and userid in (##abc)";
        // Pattern pattern = Pattern.compile("[#$&]([\\w#$&]+)");
        // Matcher matcher = pattern.matcher(sql);
        // while(matcher.find()) {
        //     System.out.println(matcher.groupCount());
        //     System.out.println(matcher.group(0));
        // }

    }
}
